package cn.jilian.admin.tool.service.impl;

import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import cn.jilian.admin.tool.domain.ColumnInfo;
import cn.jilian.admin.tool.service.DBService;
import cn.jilian.admin.tool.service.support.Formatter;
import cn.jilian.admin.tool.service.support.FormatterFactory;
import cn.jilian.common.dto.PageInfo;
import cn.jilian.common.dto.PageResult;
import cn.jilian.common.dto.SortInfo;
import cn.jilian.dao.PageQuery;

@Service
public class DBServiceImpl implements DBService {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private PageQuery pageQuery;
	
	@Value("${spi.dbms.database}")
	private String schema;

	@Override
	public PageResult<Map<String, Object>> queryTableList(PageInfo page,SortInfo sort) {
		String sql="select * from information_schema.TABLES where TABLE_SCHEMA =?";
		if(sort!=null&&StringUtils.isNotBlank(sort.getField())) {
			sql+=" order by "+sort.getField()+" "+sort.getDirect();
		}
		return pageQuery.query(sql, new Object[] {schema}, page);
	}

	
	@Override
	public PageResult<Map<String, Object>> queryTableColumns(PageInfo page, String table) {
		String sql="select * from INFORMATION_SCHEMA.COLUMNS where table_name = ? and TABLE_SCHEMA=?";
		return pageQuery.query(sql, new Object[] {table,schema},page);
	}

	@Override
	public List<Map<String, Object>> queryTableColumns(String table) {
		String sql="select * from INFORMATION_SCHEMA.COLUMNS where table_name = ? and TABLE_SCHEMA=?";
		return jdbcTemplate.queryForList(sql,table,schema);
	}

	@Override
	public int moveSeq(String table, String column, int step) {
		int max=getMaxSeq(table);
		Map<String,Object> col=getColumn(table,column);
		int seq=((Number)col.get("ORDINAL_POSITION")).intValue();
		
		int newSeq=(seq+step)%max;
		if(newSeq<=0)newSeq=max+newSeq;
		String type=(String) col.get("COLUMN_TYPE");
		StringBuilder sb=new StringBuilder("ALTER TABLE ")
				.append(table)
				.append(" MODIFY ")
				.append(column)
				.append(" ")
				.append(type);
		String defaultValue=(String) col.get("COLUMN_DEFAULT");
		if(defaultValue!=null) {
			sb.append(" default ").append(defaultValue);
		}
		String comment=(String) col.get("COLUMN_COMMENT");
		
		if(StringUtils.isNotBlank(comment)) {
			sb.append(" comment '").append(comment).append("'");
		}
		
		if(newSeq==1) {
			sb.append(" FIRST");
		}else {
			int afterSeq=newSeq>seq?newSeq:newSeq-1;
			Map<String,Object> befCol=getColumn(table,afterSeq);
			sb.append(" AFTER ").append(befCol.get("COLUMN_NAME"));
		}	
		
		jdbcTemplate.execute(sb.toString());
		return 1;
	}
	
	private Map<String,Object> getColumn(String table,int seq){
		String sql="select * from INFORMATION_SCHEMA.COLUMNS where table_name = ? and TABLE_SCHEMA=? and ORDINAL_POSITION=?";
		return jdbcTemplate.queryForMap(sql,table,schema,seq);
	}
	
	private Map<String,Object> getColumn(String table,String column) {
		String sql="select * from INFORMATION_SCHEMA.COLUMNS where table_name = ? and TABLE_SCHEMA=? and COLUMN_NAME=?";
		return jdbcTemplate.queryForMap(sql,table,schema,column);
	}
	
	private int getMaxSeq(String table) {
		String sql="select max(ORDINAL_POSITION) as MAXSEQ from INFORMATION_SCHEMA.COLUMNS where table_name = ? and TABLE_SCHEMA=?";
		return jdbcTemplate.queryForObject(sql, new Object[] {table,schema},Integer.class);
	}

	@Override
	public int updateColumn( ColumnInfo data) {
		String table=data.getTable();
		String column=data.getColumn();
		StringBuilder sb=new StringBuilder("ALTER TABLE ")
				.append(table);
		String name=(String) data.getName();
		String type=(String) data.getType();
		Integer length=data.getLength();
		Integer decimals=data.getDecimals();
		String key=data.getKey();
		String notNull=data.getNotNull();
		String defaultValue=data.getDefaultValue();
		String comment=data.getComment();
		if(StringUtils.equalsIgnoreCase(name, column)) {
			sb.append(" MODIFY column ")
			.append(column);
		}else {
			sb.append(" change column ")
			.append(column)
			.append(" ")
			.append(name);
		}
		
				
		if(StringUtils.equalsIgnoreCase("varchar", type)) {
			if(length!=null&&length>0&&length<255) {
				sb.append(" varchar(")
				.append(length)
				.append(")");
			}else {
				sb.append(" varchar(255)");
			}
		}else if(StringUtils.equalsIgnoreCase("decimal", type)) {
			length=length==null?18:length;
			sb.append(" decimal(")
			.append(length);
			if(decimals!=null) {
				sb
				.append(",").append(decimals);
			}
			sb.append(")");
		}else if (StringUtils.equalsIgnoreCase("timestamp", type)) {
			sb.append(" timestamp");
		}else {
			sb.append(" ").append(type);
		}
		
		if(StringUtils.equalsIgnoreCase(key, "PRI")) {
//			sb.append(" primary key ");
		}else if(StringUtils.equalsIgnoreCase("NO", notNull)) {
			sb.append(" NOT NULL");
		}else if(StringUtils.isNotBlank((String)defaultValue)) {
			sb.append(" DEFAULT ").append(defaultValue);
		}else {
			sb.append(" DEFAULT NULL");
		}
		if(StringUtils.isNotBlank(comment)) {
			sb.append(" comment ").append("'")
			.append(comment).append("'");
		}
		return jdbcTemplate.update(sb.toString());
	}

	@Override
	public int updateTableComment(String table,String name, String comment) {
		StringBuilder sb=new StringBuilder("ALTER TABLE ").append(table)
				.append(" comment '")
				.append(comment).append("'");
		jdbcTemplate.execute(sb.toString());
		return 1;
	}

	@Override
	public String exportCreateSql(String table) {
		List<Map<String,Object>> cols=queryTableColumns(table);
		if(CollectionUtils.isEmpty(cols))return "";
		StringBuilder sb=new StringBuilder("DROP TABLE IF EXISTS '")
				.append(table)
				.append("';\r\n") 
				.append("create table '")
				.append(table).append("'(");
		for(Map<String,Object> c:cols) {
			sb.append("\r\n  '").append(c.get("COLUMN_NAME"))
			.append("' ").append(c.get("COLUMN_TYPE"));
			if(StringUtils.equalsIgnoreCase("PRI",(String) c.get("COLUMN_KEY"))) {
				sb.append(" primary key ");
			}else {
				if(StringUtils.equalsIgnoreCase("NO", (String)c.get("IS_NULLABLE"))) {
					sb.append(" not null ");
				}
				Object column_def=c.get("COLUMN_DEFAULT");
				if(column_def!=null) {
					if(StringUtils.equalsIgnoreCase("timestamp",(String) c.get("DATA_TYPE"))) {
						sb.append(" default ").append(column_def);
					}else {
						Formatter fmt=FormatterFactory.TYPE_FORMATTER_MAP.get(c.get("DATA_TYPE"));
						sb.append(" default ").append(fmt.format(column_def));
					}
					
				}
			}
			if(StringUtils.isNotBlank((String)c.get("COLUMN_COMMENT"))) {
				sb.append(" comment '").append(c.get("COLUMN_COMMENT")).append("'");
			}
			sb.append(",");
		}
		sb.setLength(sb.length()-1);
		sb.append("\r\n)");
		Map<String,Object> tt=queryTable(table);
		if(StringUtils.isNotBlank((String)tt.get("TABLE_COMMENT"))){
			sb.append(" comment ='").append(tt.get("TABLE_COMMENT")).append("'");
		}
		sb.append(";\r\n");
		return sb.toString();
	}

	@Override
	public String exportInsertSql(String table) {
		String sql=new StringBuilder("select * from ").append(table).toString();
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		if(CollectionUtils.isEmpty(list))return "";
		StringBuilder sb=new StringBuilder();
		Table t=getInsertSqlPrefix(table);
		for(Map<String,Object> n:list) {
			sb.append(t.getInsertSqlPrefix());
			for(Map<String,Object> c:t.getList()) {
				Formatter fmt=FormatterFactory.TYPE_FORMATTER_MAP.get(c.get("DATA_TYPE"));
				fmt.append(sb, n.get(c.get("COLUMN_NAME")));
				sb.append(",");
			}
			sb.setLength(sb.length()-1);
			sb.append(");\r\n");
		}
		return sb.toString();
	}
	
	
	public static class Table{
		
		public Table(String prefix,List<Map<String,Object>> list) {
			this.insertSqlPrefix=prefix;
			this.list=list;
		}
		private String insertSqlPrefix;
		
		private List<Map<String,Object>> list;

		public String getInsertSqlPrefix() {
			return insertSqlPrefix;
		}

		public void setInsertSqlPrefix(String insertSqlPrefix) {
			this.insertSqlPrefix = insertSqlPrefix;
		}

		public List<Map<String, Object>> getList() {
			return list;
		}

		public void setList(List<Map<String, Object>> list) {
			this.list = list;
		}
		
		
	}
	private Table getInsertSqlPrefix(String table) {
		StringBuilder sb=new StringBuilder("insert into ")
				.append(table).append("(");
		List<Map<String,Object>> list=queryTableColumns(table);
		for(Map<String,Object> n:list) {
			String colname=(String) n.get("COLUMN_NAME");
			sb.append(colname).append(",");
		}
		sb.setLength(sb.length()-1);
		sb.append(")values(");
		String prefix= sb.toString();
		return new Table(prefix,list);
	}

	@Override
	public int deleteTable(String table) {
		String sql=new StringBuilder("drop table ").append(table).toString();
		jdbcTemplate.execute(sql);
		return 1;
	}

	@Override
	public int renameTable(String table, String name) {
		StringBuilder sb=new StringBuilder("ALTER TABLE ")
				.append(table)
				.append(" RENAME ")
				.append(name);
		jdbcTemplate.execute(sb.toString());
		return 1;
	}


	@Override
	public Map<String, Object> queryTable(String table) {
		String sql="select * from information_schema.TABLES where TABLE_SCHEMA =? and TABLE_NAME=?";
		return jdbcTemplate.queryForMap(sql,schema,table);
	}


	@Override
	public int changeTableName(String table, String name) {
		String sql=new StringBuilder("change table ").append(table).append(" rename ").append(name).toString();
		jdbcTemplate.execute(sql);
		return 1;
	}


	@Override
	public int addColumn(ColumnInfo columnInfo) {
		if(StringUtils.isBlank(columnInfo.getTable())) {
			return -1;
		}
		Integer length=columnInfo.getLength();
		String type=columnInfo.getType();
		Integer decimals=columnInfo.getDecimals();
		String notNull=columnInfo.getNotNull();
		String defaultValue=columnInfo.getDefaultValue();
		String comment=columnInfo.getComment();
		String key=columnInfo.getKey();
		StringBuilder sb=new StringBuilder("alter table ")
				.append(columnInfo.getTable())
				.append(" add column ")
				.append(columnInfo.getName());
		if(StringUtils.equalsIgnoreCase("varchar", columnInfo.getTable())) {
			
			if(length!=null&&length>0&&length<255) {
				sb.append(" varchar(")
				.append(length)
				.append(")");
			}else {
				sb.append(" varchar(255)");
			}
		}else if(StringUtils.equalsIgnoreCase("decimal", type)) {
			length=length==null?18:length;
			sb.append(" decimal(")
			.append(length);
			if(decimals!=null) {
				sb
				.append(",").append(decimals);
			}
			sb.append(")");
		}else if (StringUtils.equalsIgnoreCase("timestamp", type)) {
			sb.append(" timestamp");
		}else {
			sb.append(" ").append(type);
		}
		
		if(StringUtils.equalsIgnoreCase(key, "PRI")) {
			sb.append(" primary key ");
		}else if(StringUtils.equalsIgnoreCase("YES", notNull)) {
			sb.append(" NOT NULL");
		}else if(StringUtils.isNotBlank((String)defaultValue)) {
			sb.append(" DEFAULT ").append(defaultValue);
		}else {
			sb.append(" DEFAULT NULL");
		}
		if(StringUtils.isNotBlank(comment)) {
			sb.append(" comment ").append("'")
			.append(comment).append("'");
		}
		
		jdbcTemplate.execute(sb.toString());
		return 1;
	}


	@Override
	public void createTable(String name,String comment) {
		StringBuilder sb=new StringBuilder("CREATE TABLE ")
				.append(name)
				.append("(sid bigint(20) not null comment '编号',")
				.append("fid bigint(20) not null comment '分组',")
				.append("seq bigint(20) not null comment '排序',")
				.append("name varchar(20) not null comment '名称',")
				.append("code varchar(20) not null comment '编码',")
				.append("text varchar(20) not null comment '文本',") 
				.append("meta bigint(20) not null comment '元ID',") 
				.append("rank bigint(20) not null comment '类型',") 
				.append("flag int(11) not null comment '状态',")
				.append("tims timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP comment '时间',")
				.append(" PRIMARY KEY (`sid`))ENGINE=InnoDB DEFAULT CHARSET=utf8");
		if(StringUtils.isNotBlank(comment)) {
			sb.append(" COMMENT='").append(comment).append("'");
		}
		jdbcTemplate.execute(sb.toString());
		
	}


	@Override
	public int deleteColumns(String name, Collection<String> columns) {
		StringBuilder sb=new StringBuilder("ALTER TABLE ")
				.append(name);
		for(String c:columns) {
			sb.append(" DROP COLUMN ").append(c).append(",");
		}
		sb.setLength(sb.length()-1);
		jdbcTemplate.execute(sb.toString());
		return 1;
	}


	@Override
	public PageResult<Map<String, Object>> queryRecords(PageInfo page, String table) {
		String sql=new StringBuilder("select * from ").append(table).toString();
		return pageQuery.query(sql, page);
	}


	@Override
	public int deleteTables(Collection<String> tables) {
		if(CollectionUtils.isNotEmpty(tables)) {
			int rs=0;
			for(String table:tables) {
				rs+=deleteTable(table);
			}
			return rs;
		}
		return 0;
	}

	
}
